-- ==========================================
-- 银行系统数据库初始化脚本（admin 用户执行）
-- 执行方式：psql -U admin -f init_admin-schema.sql
-- 文件必须 UTF-8 编码
-- ==========================================

SET search_path TO admin, public;

-- 1. 创建交易类型 ENUM（安全创建）
DO $$
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'transaction_type') THEN
            EXECUTE 'CREATE TYPE transaction_type AS ENUM (''FIXED_DEPOSIT'',''DEPOSIT'',''WITHDRAW'',''TRANSFER'')';
        END IF;
    END $$;

-- 2. 用户表（新增 password 字段）
DROP TABLE IF EXISTS users CASCADE;
CREATE TABLE users (
                       user_id SERIAL PRIMARY KEY,
                       name VARCHAR(50) NOT NULL,
                       phone VARCHAR(11) NOT NULL UNIQUE CHECK (phone ~ '^[0-9]{11}$'),
                       id_card VARCHAR(18) NOT NULL UNIQUE CHECK (id_card ~ '^[0-9Xx]{18}$'),
                       gender CHAR(1) CHECK (gender IN ('男','女')),
                       password CHAR(6) NOT NULL CHECK (password ~ '^[0-9]{6}$'),
                       create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. 账户表（增加 fixed_balance 用于定期存款）
DROP TABLE IF EXISTS accounts CASCADE;
CREATE TABLE accounts (
                          account_id SERIAL PRIMARY KEY,
                          card_number CHAR(19) NOT NULL UNIQUE,
                          open_date DATE NOT NULL DEFAULT CURRENT_DATE,
                          user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
                          balance DECIMAL(18,2) NOT NULL DEFAULT 0 CHECK (balance >= 0),      -- 活期余额
                          fixed_balance DECIMAL(18,2) NOT NULL DEFAULT 0 CHECK (fixed_balance >= 0), -- 定期存款金额
                          status VARCHAR(10) NOT NULL DEFAULT '正常' CHECK (status IN ('正常','冻结','销户'))
);

-- 4. 交易表
DROP TABLE IF EXISTS transactions CASCADE;
CREATE TABLE transactions (
                              transaction_id SERIAL PRIMARY KEY,
                              type transaction_type NOT NULL,
                              time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                              source_account CHAR(19) REFERENCES accounts(card_number),
                              target_account CHAR(19) REFERENCES accounts(card_number),
                              amount DECIMAL(18,2) NOT NULL CHECK (amount > 0),
                              maturity_interest DECIMAL(12,2),
                              remark TEXT,
                              maturity_date TIMESTAMP                  -- 定期存款到期时间
);

-- 5. 触发器函数（区分定期/活期存款）
CREATE OR REPLACE FUNCTION update_balance()
    RETURNS TRIGGER AS $$
DECLARE
    src_balance DECIMAL(18,2);
BEGIN
    -- 活期存款：直接增加账户余额
    IF NEW.type = 'DEPOSIT' AND NEW.target_account IS NOT NULL THEN
        UPDATE accounts
        SET balance = balance + NEW.amount
        WHERE card_number = NEW.target_account;

        -- 定期存款：增加 fixed_balance（冻结金额），不改变活期余额
    ELSIF NEW.type = 'FIXED_DEPOSIT' AND NEW.target_account IS NOT NULL THEN
        UPDATE accounts
        SET fixed_balance = fixed_balance + NEW.amount
        WHERE card_number = NEW.target_account;

        -- 取款
    ELSIF NEW.type = 'WITHDRAW' AND NEW.source_account IS NOT NULL THEN
        SELECT balance INTO src_balance FROM accounts WHERE card_number = NEW.source_account;
        IF src_balance IS NULL THEN
            RAISE EXCEPTION '账户不存在（卡号=%）', NEW.source_account;
        END IF;
        IF src_balance < NEW.amount THEN
            RAISE EXCEPTION '余额不足，无法取款（卡号=%）', NEW.source_account;
        END IF;
        UPDATE accounts SET balance = balance - NEW.amount WHERE card_number = NEW.source_account;

        -- 转账
    ELSIF NEW.type = 'TRANSFER' AND NEW.source_account IS NOT NULL AND NEW.target_account IS NOT NULL THEN
        SELECT balance INTO src_balance FROM accounts WHERE card_number = NEW.source_account;
        IF src_balance IS NULL THEN
            RAISE EXCEPTION '源账户不存在（卡号=%）', NEW.source_account;
        END IF;
        IF src_balance < NEW.amount THEN
            RAISE EXCEPTION '余额不足，无法转账（卡号=%）', NEW.source_account;
        END IF;
        UPDATE accounts SET balance = balance - NEW.amount WHERE card_number = NEW.source_account;
        UPDATE accounts SET balance = balance + NEW.amount WHERE card_number = NEW.target_account;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 6. 触发器
DROP TRIGGER IF EXISTS trg_update_balance ON transactions;
CREATE TRIGGER trg_update_balance
    AFTER INSERT ON transactions
    FOR EACH ROW
EXECUTE FUNCTION update_balance();

-- 7. 测试数据（定期/活期存款示例）
INSERT INTO users (name, phone, id_card, gender, password) VALUES
                                                               ('张三','13800138000','110101199001011234','男','123456'),
                                                               ('李四','13900139000','110101199102022345','女','654321');

INSERT INTO accounts (card_number, open_date, user_id, balance, fixed_balance) VALUES
                                                                                   ('6228480000000000001','2020-01-01',1,5000.00,0.00),
                                                                                   ('6228480000000000002','2021-02-02',2,8000.00,0.00);

-- NOTE: 下面的卡号必须用单引号（字符串），以匹配 CHAR(19)
INSERT INTO transactions (type, time, source_account, target_account, amount, maturity_interest, remark, maturity_date) VALUES
                                                                                                                            ('FIXED_DEPOSIT','2025-10-10 10:00:00', NULL, '6228480000000000001', 5000.00, 300.00, '一年期定期存款', '2026-10-10'),
                                                                                                                            ('DEPOSIT','2025-10-15 09:00:00', NULL, '6228480000000000002', 1000.00, 10.00, '活期账户存入',NULL),
                                                                                                                            ('FIXED_DEPOSIT','2025-11-01 08:30:00', NULL, '6228480000000000001', 20000.00, 800.00, '半年期定期存款', '2026-05-01'),
                                                                                                                            ('DEPOSIT', '2025-11-02 10:00:00', NULL, '6228480000000000002', 3000.00, 15.00, '活期账户转入',  NULL),
                                                                                                                            ('TRANSFER','2025-11-05 15:00:00', '6228480000000000002', '6228480000000000001', 5000.00, 0.00, '偿还李四借款',NULL),
                                                                                                                            ('TRANSFER','2025-11-08 14:00:00', '6228480000000000001', '6228480000000000002', 8000.00, 0.00, '项目回款到账',NULL);

-- 8. 设置属主
ALTER TABLE users OWNER TO admin;
ALTER TABLE accounts OWNER TO admin;
ALTER TABLE transactions OWNER TO admin;

\echo '银行数据库初始化完成。'
